-- @owner: zou_jialiang050
-- @date: 2024/4/2
-- @testpoint: pipelined函数中使用pipelined作为对象名
-- @modify: modified by zhangbingliang at 2025.01,优化用例适配代码变更
--step1:创建嵌套表类型;expect:成功
create or replace type type_0005 as (pipelined integer, c2 tinyint, c3 numeric(10, 4), c4 decimal(12),
c5 number(10,4), c6 float(10), c7 bit(1), c8 dec(10,3), c9 boolean, c10 char(10),
c11 nchar(10), c12 varchar(200), c13 varchar2(20), c14 text, c15 blob, c16 date, c17 time);
create or replace type pipelined as table of type_0005;

--step2:创建管道函数并查看返回值;expect:成功
create or replace function pipelined(count in number)
returns pipelined pipelined
as $$
declare pipelined type_0005;
begin
for i in 1 .. count loop
pipelined.pipelined = i + 1;
pipelined.c2 = i + 1;
pipelined.c3 = i + 0.005;
pipelined.c4 = i - 0.5;
pipelined.c5 = i + 0.7;
pipelined.c6 = i * 0.5;
pipelined.c7 = 1;
pipelined.c8 = (i / 0.5);
pipelined.c9 = true;
pipelined.c10 = '1sadf1234';
pipelined.c11 = 'sdfwdf';
pipelined.c12 = '123wsdfwaerwefasd';
pipelined.c13 = 'sdfwersdf';
pipelined.c14 = 'exdgeww';
pipelined.c15 = '1'::blob;
pipelined.c16 = date'2022-01-01';
pipelined.c17 = time'11:11:11';
pipe row(pipelined);
end loop;
return;
end;
$$ language plpgsql;
/

--step3:调用pipelined函数并查看返回类型;expect:成功
select pipelined(3);
select * from pipelined(3);
select * from table(pipelined(3));
select pipelined from table(pipelined(3));
select pg_typeof(pipelined(3));

--step4:使用alter修改函数名称;expect:成功
alter function pipelined(count in number) rename to pipelined_1;
select pipelined_1(3);
select * from pipelined_1(3);
select * from table(pipelined_1(3));

--step5:使用create table as创建表;expect:成功
drop table if exists t_pipelined_0005;
create table t_pipelined_0005 as select * from pipelined_1(1);
insert into t_pipelined_0005 (select * from table(pipelined_1(1)));

--step6:清理环境;expect:成功
drop type if exists pipelined cascade;
drop type if exists type_0005 cascade;
drop function if exists pipelined_1;
drop table if exists t_pipelined_0005;
